package yawning.mew.equipment;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map.Entry;

import yawning.mew.equipment.RawItem.Property;

public class DatabaseItemSource extends ItemSource {
	private Connection mConnection;
	private PreparedStatement mInsertItem;
	private PreparedStatement mSelectItem;
	private PreparedStatement mInsertProperty;
	private PreparedStatement mSelectProperty;
	
	public DatabaseItemSource() {
		try {
			Class.forName("smallsql.database.SSDriver");
			mConnection = java.sql.DriverManager.getConnection("jdbc:smallsql:itemdb?create=true");
			
			// Test if table exists
			try {
				mSelectItem = mConnection.prepareStatement("SELECT * FROM items WHERE id = ?");
			}
			catch (Exception e) {
				Statement statement = mConnection.createStatement();;
				statement.execute("CREATE TABLE items ( id INT, name NVARCHAR(256))");
				statement.execute("CREATE TABLE itemproperties ( item_id INT, property VARCHAR(64), value INT)");
			}

			mSelectItem = mConnection.prepareStatement("SELECT * FROM items WHERE id = ?");
			mInsertItem = mConnection.prepareStatement("INSERT INTO items (id, name) VALUES (?, ?)");
			mSelectProperty = mConnection.prepareStatement("SELECT * FROM itemproperties WHERE item_id = ?");
			mInsertProperty = mConnection.prepareStatement("INSERT INTO itemproperties (item_id, property, value) VALUES (?, ?, ?)");
		}
		catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public RawItem getById(int id) {
		try {
			mSelectItem.setInt(1, id);
			ResultSet rs = mSelectItem.executeQuery();
			if (rs.next()) {
				RawItem item = new RawItem();
				item.id = id;
				item.name = rs.getString("name");
				item.properties = new HashMap<Property,Integer>();
				mSelectProperty.setInt(1, id);
				rs = mSelectProperty.executeQuery();
				while (rs.next()) {
					String p = rs.getString("property");
					int v = rs.getInt("value");
					item.properties.put(Property.valueOf(p), v);
				}
				return item;
			}

			RawItem item = mNext.getById(id);
			if (item != null) {
				mInsertItem.setInt(1, id);
				mInsertItem.setString(2, item.name);

				for (Entry<Property,Integer> e : item.properties.entrySet()) {
					mInsertProperty.setInt(1, id);
					mInsertProperty.setString(2, e.getKey().toString());
					mInsertProperty.setInt(3, e.getValue());
					mInsertProperty.execute();
				}

				mInsertItem.execute();
			}
			return item;
		}
		catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}
